# -*- coding: utf-8 -*-
__author__ = 'sunnychou'
__date__ = '2019/7/29 10:50'
#财务管理相关配置文件

finace_cal_fields = {
#校验crm系统中k3数据库中表月份数据是否存在
    'crm_k3_month_tb_exist':{
            "caigou_fkd":"select  distinct tjmonth from  k3cgfkds",
            "kh_business":"select distinct ymonth  from  k3jyqks",
    },
    #采购付款单统计表
    'caigou_fkd_rp':{
               "caigou_fkd_insert":"insert into k3cgfkds(id,created, pjcode, sname, kqtype, pp, bydde,  byrke, "
                                   " byyfk, byyinfk,  byysp, wsfp, xyyjyfk, tjmonth, paytype)  "
                                   "values('%s', '%s','%s', '%s','%s','%s', '%s', '%s', '%s', '%s', '%s', '%s','%s', '%s', '%s')",

               #获取采购订单的供应商信息，获取上月
               "caigoud_gys_select":"select  distinct  k3_gongys.code,k3_caigoud.gongys_name, k3_caigoud.brand, k3_caigoud.jiesuan_type, k3_caigoud.bibie  from k3_caigoud, k3_gongys where "
                                    "k3_caigoud.gongys_name = k3_gongys.name and k3_caigoud.sjsj='%s'; ",
                #计算本月订单额（从上月的采购订单中获取价税合计总和）   --- 检测：正确
               "caigoud_dingdan_bymonth":"select sum(cast(jiashui_heji as float)) as dingdane,  gongys_name from  k3_caigoud where  k3_caigoud.sjsj='%s' and gongys_name !='' group by gongys_name",
                # 计算入库金额（从上月的外购入库单中获取含税金额）    --- 检测：正确
               "caigoud_ruku_jine_bymonth": "select sum(cast(hanshui_jine as float)), gongys from   k3_waigourk where sjsj='%s' and gongys !='' group by gongys",
               #计算本月已付款                                     ----  检测：正确
               "caigoud_yifukuan_bymonth": "select  sum(cast(shifu_jine as float)) as sum_danju_jine,supplier_name   from k3_fukd where danju_code !='' and sjsj='%s'  group by supplier_name",
                 #计算本月已收票                                      -- 检测：正确
               "caigoufp_ysfp_bymonth":"select  sum(cast(jshj as float)) as sum_jshj, ghdw   from k3_cgfp where sjsj='%s' and ghdw !=''  group by ghdw",
               #计算本月未收发票                                  -- 检测：正确
               "caigou_weishou_fp":"select  sum((cast(hanshui_jine as float))), gongys   from  k3_waigourk where sjsj ='%s' and guige_xinghao != ''  and kaipiao_number != shishou_number and hanshui_rate !=0   group by gongys",

    },
    'jine_by_jiesuan_type':{
               #1. 采购订单
               #款到发货 计算应付款, 从销售订单获取
               "yingfuk_by_kdfh_bymonth":"select sum(cast(jiashui_heji as float)),  gongys_name  from   k3_caigoud where sjsj='%s' and  caigou_code !=''  group by gongys_name",
                #计算应付款  月结（采购发票）
                "yingfuk_with_yuejie_bymonth":"select sum(cast(jshj as float)),  ghdw  from   k3_cgfp  where sjsj='%s' and  ggxh !=''  group by ghdw",
                #计算应付款 外购入库（货到付款）
                "yingfuk_with_waigou_bymonth":"select  sum(cast(hanshui_jine as float)),    gongys  from  k3_waigourk  where sjsj = '%s' and guige_xinghao != '' group by gongys",

                #2. 销售订单
                #款到发货，从销售订单计算应收款
                "ysk_by_kdfh_bymonth":"select sum(cast(jshj as float)) as jshj_sum,  ghdw  from   k3_xsdd where sjsj='%s' and ghdw != ''   group by ghdw",
                #货到付款，从销售出库计算应收款
                "ysk_by_hdfk_bymonth":"select  sum(cast(xsje as float)) as xsje_sum,    ghdw  from  k3_xsckd  where sjsj = '%s'  group by ghdw",
                #月结，从销售发票中计算应收款
                "ysk_by_mtyj_bymonth":"select   sum(cast(jshj as float)) as jshj_sum,    ghdw   from k3_xsfp where sjsj= '%s' group by ghdw",

               #3. 按照时间范围进行查询应收款
                "ysk_by_hdfk_byrange":"select  sum(cast(xsje as float)) as xsje_sum,    ghdw  from  k3_xsckd  where  sjsj BETWEEN '%s' and '%s'  group by ghdw  ",
                "ysk_by_mtyj_bytrange":"select   sum(cast(jshj as float)) as jshj_sum,    ghdw   from k3_xsfp where sjsj BETWEEN '%s' and '%s'  group by ghdw",
                "ysk_by_kdfh_bytrange":"select sum(cast(jshj as float)) as jshj_sum,  ghdw  from   k3_xsdd where sjsj BETWEEN '%s' and '%s' and ghdw != ''   group by ghdw",
    },
    'kh_business':{
        "business_jyqk_insert":"insert into  k3jyqks(id, crttime, scode, kehuname, depart, khname, kuanqi,  mhetonge, monthml, ycke, ymaoli,"
                               "yske, skyml, myysk, yfdqysk,yfcqys,yfykfp,paytype,ymonth) "
                               "values('%s', '%s','%s', '%s', '%s', '%s', '%s', '%s', '%s',  '%s', '%s','%s', '%s','%s', '%s','%s', '%s','%s', '%s')",
        #查询收款单基本信息（经营情况报表以收款单为母表）
        "business_skd_select":"select  distinct khdm, kh, ywy, 	jsfs, bm, bb   from k3_skd where khdm != '' and  sjsj='%s'",

        #获取采购订单的产品型号和未税单价
        "weishui_price_select":"select  guige_xinhao, weishui_uprice  from  k3_caigoud",

        #获取月合同额               --------------------  检测结果：正确
        "business_k3_xsdd_select":"select sum(cast(jshj as float)) as sum_jshj,  ghdw   from k3_xsdd  where sjsj = '%s' and ghdw != ''  group by ghdw",

        #本月合同毛利                   --------------------  检测结果：
        "hetong_maoli_select":"select   ghdw, ggxh, sl, wsje   from k3_xsdd where  sjsj='%s'",

        #本月出库                    ------------------  检测结果：大部分正确
        "business_k3_chukue_select":"select  sum(cast(xsje as float))  as sum_xsje, ghdw     from k3_xsckd where sjsj = '%s' and ghdw != '' group by ghdw",

        #本月出库毛利
        "busines_k3_ck_wsje_select":"select    sum(cast(wsje as float) - cast(cb as float)) as ckml, ghdw  from   k3_xsckd  where sjsj = '%s' and ghdw != '' group by ghdw",

        #本月收款
        "business_k3_shoukuandan":"select  sum(cast(jsssje as float)) as s_sj, khdm  from  k3_skd where sjsj='%s' and khdm != ''  group by khdm",

        #本月收款毛利
        #收款单数据
        "sdk_ml_base_select":"select  kh, ydlx, ggxh, jsssje, sl, bb    from  k3_skd  where sjsj='%s' and kh!=''",
        #发票类型，销售出库获取
        "sdk_ml_xsck_select":"select top 1  dwcb, slv    from k3_xsckd where ggxh != '' and ggxh='%s' order by ckrq desc",
        #订单类型，采购订单获取
        "sdk_ml_cgdd_select":"select top 1 weishui_uprice, shui_rate  from  k3_caigoud where  guige_xinhao !='' and guige_xinhao='%s'  order by  caigou_date desc",

        #本月应收款
        #从上述的ysk_by_kdfh_bymonth

        #下月到期应收

        #下月超期应收

        #下月应开票
        "ykfp_next_month_select":"select  sum((cast(sfsl as float) -  cast(kpsl as float)) *  cast(xsdj as float)),  ghdw   from  k3_xsckd   where sjsj = '%s' and cast(slv as float)  != 0.0 and sfsl != kpsl  group by ghdw",

    },
    "skjlhsmx":{
        #校验统计年月数据是否存在
        "check_db_sql":"SELECT   DISTINCT ghdw from  k3skjllr  WHERE tjmonth='%s'",
        "insert_skjllrhs": "insert into k3skjllr(ghdw,hth,ckrq,xbjmxdh,pingpai,cpjl,cpmc,ggxh,bz,sfsl,xsdj,ssje,jlbl,jle,fourcjj,thirdcjj,lkrq,bumen,ywy,jsfs，shuilv,tjmonth,id)"
                           " values('%s', '%s', '%s', '%s', '%s', '%s', '%s','%s', '%s', '%s', '%s', '%s', '%s', '%s','%s', '%s', '%s', '%s', '%s', '%s', '%s','%s','%s','%s')",
    },
    "lastyear_qk":{
        "select_lastyear_qk":"select  kh_code, lj2017qk, lj2018qk  from k3_khljqk",
    }
}


